package com.qdlc.p2p.dal.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.google.common.collect.Maps;
import com.qdlc.p2p.common.constant.BorrowRepaymentStatus;
import com.qdlc.p2p.common.constant.Global;
import com.qdlc.p2p.common.exception.BorrowException;
import com.qdlc.p2p.common.util.BigDecimalUtil;
import com.qdlc.p2p.common.util.DateUtil;
import com.qdlc.p2p.common.util.Page;
import com.qdlc.p2p.common.util.StringUtil;
import com.qdlc.p2p.dal.common.dao.jpa.BaseDaoImpl;
import com.qdlc.p2p.dal.common.model.jpa.PageDataList;
import com.qdlc.p2p.dal.common.model.jpa.QueryParam;
import com.qdlc.p2p.dal.common.model.jpa.SearchFilter;
import com.qdlc.p2p.dal.common.model.jpa.SearchFilter.Operators;
import com.qdlc.p2p.dal.dao.BorrowRepaymentDao;
import com.qdlc.p2p.dal.dto.BorrowRepayment;
import com.qdlc.p2p.dal.model.BorrowModel;
import com.qdlc.p2p.dal.model.BorrowRepaymentModel;


/**
 * 还款
 *
 * @author cx
 * @version 2.0
 *          2014-11-13
 */
@Repository("borrowRepaymentDao")
public class BorrowRepaymentDaoImpl extends BaseDaoImpl<BorrowRepayment> implements BorrowRepaymentDao {

    @Override
    public BorrowRepayment findById(long id) {
        BorrowRepayment borrowRepayment = super.find(id);
        if (borrowRepayment.getStatus() == BorrowRepaymentStatus.STATUS_YES_REPAY) {
            throw new BorrowException("该还款计划已经还款", 1);
        }
        return borrowRepayment;
    }

    @Override
    public void updateBorrowRepaymentByStatus(BorrowRepayment repay) {
        StringBuffer buffer = new StringBuffer(
                "UPDATE " + Global.DB_PREFIX + "borrow_repayment SET status = :status,repayment_yes_time = :repaymentYesTime,");
        buffer.append("type = :type, repayment_user_id = :repaymentUserId,");
        buffer.append("repayment_yes_account = :repaymentYesAccount WHERE ( status = "
                + BorrowRepaymentStatus.STATUS_WAIT_REPAY + " or status = "
                + BorrowRepaymentStatus.STATUS_MANAGE_REPAY + " or status = "
                + BorrowRepaymentStatus.STATUS_MANAGE_COMPENSATE + " or status = "
                + BorrowRepaymentStatus.STATUS_PART_REPAY + " or status = "
                + BorrowRepaymentStatus.STATUS_REPAY_ING + ") AND id = :id");
        Query query = em.createNativeQuery(buffer.toString());
        query.setParameter("status", repay.getStatus());
        query.setParameter("repaymentYesTime", repay.getRepaymentYesTime());
        query.setParameter("repaymentYesAccount", repay.getRepaymentYesAccount());
        query.setParameter("type", repay.getType());
        query.setParameter("repaymentUserId", repay.getRealRepayer().getUserId());
        query.setParameter("id", repay.getId());
        query.executeUpdate();
    }

    @Override
    public boolean hasRepaymentAhead(int period, long borrowId) {
        QueryParam param = QueryParam.getInstance();
        param.addParam("period", Operators.LT, period);
        param.addParam("borrow.id", borrowId);
        param.addParam("status", 0);
        int count = super.countByCriteria(param);
        if (count > 0) {
            return true;
        }
        return false;
    }

    @Override
    public boolean updateStatus(int afterStatus, int beforeStatus, long repayId) {
        String sql = "UPDATE " + Global.DB_PREFIX + "borrow_repayment SET status = :status WHERE status = :before_status  AND (web_status = 3 OR web_status = 1) AND id = :id";
        Query query = em.createNativeQuery(sql);
        query.setParameter("id", repayId);
        query.setParameter("status", afterStatus);
        query.setParameter("before_status", beforeStatus);
        int result = query.executeUpdate();
        if (result != 1) {
            return false;
        } else {
            // 更新缓存
            em.refresh(this.find(repayId));
        }
        return true;
    }

    @Override
    public double getUserBorrowTotal(long userId) {
        StringBuffer buffer = new StringBuffer(
                "SELECT SUM(b.capital) FROM BorrowRepayment b WHERE b.user.userId = :userId group by b.user.userId");
        Query query = em.createQuery(buffer.toString());
        query.setParameter("userId", userId);
        return Double.parseDouble(query.getSingleResult().toString());
    }

    @Override
    public double getUserBorrowRepayTotal(long userId) {
        StringBuffer buffer = new StringBuffer(
                "SELECT SUM(b.repaymentAccount+b.lateInterest)  FROM BorrowRepayment b WHERE b.user.userId = :userId and status in (:status1,:status2)");
        Query query = em.createQuery(buffer.toString());
        query.setParameter("userId", userId);
        query.setParameter("status1", 0);
        query.setParameter("status2", 2);
        Object ret = query.getSingleResult();
        if (ret == null) return 0;
        return Double.parseDouble(ret.toString());
    }

    @Override
    public BorrowRepayment find(long borrowId, int period) {
        QueryParam param = QueryParam.getInstance();
        param.addParam("borrow.id", borrowId);
        param.addParam("period", period);
        return super.findByCriteriaForUnique(param);
    }

    @Override
    public double getRemainderCapital(long borrowId) {
        String jpql = "select sum(capital) from BorrowRepayment br where br.status in(0,2)  and br.borrow.id= ?1 ";
        Query q = em.createQuery(jpql).setParameter(1, borrowId);
        Object ret = q.getSingleResult();
        if (ret == null) return 0;
        return Double.parseDouble(ret.toString());
    }

    @Override
    public double getwaitRpayInterest(long borrowId, int period) {
        String jpql = "select sum(interest) from BorrowRepayment br where br.status=0  and br.borrow.id= ?1  and (br.period >=?2 and br.period <=?3)";
        Query q = em.createQuery(jpql).setParameter(1, borrowId);
        q.setParameter(2, period);
        q.setParameter(3, period + 1);
        Object ret = q.getSingleResult();
        if (ret == null) return 0;
        return Double.parseDouble(ret.toString());
    }

    @Override
    public double getRemainderInterest(long borrowId) {
        String jpql = "select sum(interest) from BorrowRepayment br where br.status in (0,2)  and br.borrow.id= ?1 ";
        Query q = em.createQuery(jpql).setParameter(1, borrowId);
        Object ret = q.getSingleResult();
        if (ret == null) return 0;
        return Double.parseDouble(ret.toString());
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    @Override
    public BorrowRepaymentModel getRepayStatistics(long userId) {
        BorrowRepaymentModel bm = new BorrowRepaymentModel();
        HashMap data = new HashMap();
        List list = new ArrayList();
        //获取本月待还sql
        String jpqlMonth = "select ifnull(sum(repayment_account),0),count(id) from " + Global.DB_PREFIX + "borrow_repayment br where br.status=0  and br.user_id= ?1 "
                + "and month(br.repayment_time)=month(now()) AND year(br.repayment_time)=year(now())";
        Query q = em.createNativeQuery(jpqlMonth).setParameter(1, userId);
        list = q.getResultList();
        double monthAccount = 0;
        int monthCount = 0;
        if (list != null && list.size() > 0) {
            Iterator iterator = list.iterator();
            while (iterator.hasNext()) {
                Object[] row = (Object[]) iterator.next();
                monthAccount = Double.parseDouble(row[0].toString());
                monthCount = Integer.parseInt(row[1].toString());
            }
        }
        data.put("monthAccount", monthAccount);
        data.put("monthCount", monthCount);
        //获取下月待还sql
        String jpqlNextMonth = "select ifnull(sum(repayment_account),0),count(id) from " + Global.DB_PREFIX + "borrow_repayment br where br.status=0  and br.user_id= ?1 "
                + " and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( br.repayment_time, '%Y%m' ) ) =-1";
        q = em.createNativeQuery(jpqlNextMonth).setParameter(1, userId);
        list = q.getResultList();
        double nextMonthAccount = 0;
        int nextMonthCount = 0;
        if (list != null && list.size() > 0) {
            Iterator iterator = list.iterator();
            while (iterator.hasNext()) {
                Object[] row = (Object[]) iterator.next();
                nextMonthAccount = Double.parseDouble(row[0].toString());
                nextMonthCount = Integer.parseInt(row[1].toString());
            }
        }
        data.put("nextMonthAccount", nextMonthAccount);
        data.put("nextMonthCount", nextMonthCount);
        //获取剩余待收sql
        String jpqlOther = "select ifnull(sum(repayment_account),0),count(id) from " + Global.DB_PREFIX + "borrow_repayment br where br.status=0  and br.user_id= ?1 "
                + " and (PERIOD_DIFF( date_format( br.repayment_time, '%Y%m' ) ,  date_format( now( ) , '%Y%m' )) >1 "
                + "or PERIOD_DIFF( date_format( br.repayment_time, '%Y%m' ) ,  date_format( now( ) , '%Y%m' )) < 0)";
        q = em.createNativeQuery(jpqlOther).setParameter(1, userId);
        list = q.getResultList();
        double otherMonthAccount = 0;
        int otherMonthCount = 0;
        if (list != null && list.size() > 0) {
            Iterator iterator = list.iterator();
            while (iterator.hasNext()) {
                Object[] row = (Object[]) iterator.next();
                otherMonthAccount = Double.parseDouble(row[0].toString());
                otherMonthCount = Integer.parseInt(row[1].toString());
            }
        }
        data.put("otherMonthAccount", otherMonthAccount);
        data.put("otherMonthCount", otherMonthCount);
        double allMonthAccount = BigDecimalUtil.add(monthAccount, nextMonthAccount, otherMonthAccount);
        int allMonthCount = monthCount + nextMonthCount + otherMonthCount;
        data.put("allMonthAccount", allMonthAccount);
        data.put("allMonthCount", allMonthCount);
        bm.setRepayData(data);
        return bm;
    }

    @Override
    public double getRepayByMonth(int month, long userId) {
        String jpql = "select sum(repaymentAccount) from BorrowRepayment br where br.status=0 and br.user.userId= ?1 and month(br.repaymentTime)=?2 AND year(br.repaymentTime)=year(now())";
        Query q = em.createQuery(jpql).setParameter(1, userId).setParameter(2, month);
        Object ret = q.getSingleResult();
        if (ret == null) {
            return 0;
        }
        return (Double) ret;
    }

    @SuppressWarnings("unchecked")
    @Override
    public BorrowRepayment getNextRepayByUserId(long userId) {
        String jpql = "from BorrowRepayment br where br.status=0 and br.user.userId= ?1 and repaymentTime >= now() order by repaymentTime asc";
        Query query = em.createQuery(jpql);
        query.setParameter(1, userId);
        List<BorrowRepayment> list = query.getResultList();
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    @Override
    public BorrowRepaymentModel getBRMByCollectTime(BorrowRepaymentModel bm, Date nextRepayTime, long userId) {
        String jpql = "select ifnull(sum(repayment_account),0),count(id) from " + Global.DB_PREFIX + "borrow_repayment br where br.status=0  and br.user_id= ?1 "
                + "  AND (DATE_FORMAT( br.repayment_time, '%Y%m%d' ) = DATE_FORMAT( ?2 , '%Y%m%d' ))";
        Query q = em.createNativeQuery(jpql).setParameter(1, userId).setParameter(2, nextRepayTime);
        List list = new ArrayList();
        list = q.getResultList();
        double account = 0;
        int count = 0;
        if (list != null && list.size() > 0) {
            Iterator iterator = list.iterator();
            while (iterator.hasNext()) {
                Object[] row = (Object[]) iterator.next();
                account = Double.parseDouble(row[0].toString());
                count = Integer.parseInt(row[1].toString());
            }
        }
        bm.setNextRepayAccount(account);
        bm.setNextRepayCount(count);
        return bm;
    }

    @Override
    public void updateBorrowRepaymentStatusAndWebStatus(
            BorrowRepayment borrowRepayment) {
        StringBuffer buffer = new StringBuffer(
                "UPDATE " + Global.DB_PREFIX + "borrow_repayment SET status = ?1,repayment_yes_time = ?2,");
        buffer.append("web_status = ?3 WHERE status = 0 AND borrow_id = ?4");
        Query query = em.createNativeQuery(buffer.toString());
        query.setParameter(1, 1);
        query.setParameter(2, DateUtil.getNow());
        query.setParameter(3, 1);
        query.setParameter(4, borrowRepayment.getBorrow().getId());
        query.executeUpdate();
    }

    @Override
    public void updateBorrowRepaymentStatus(BorrowRepayment repay) {
        StringBuffer buffer = new StringBuffer(
                "UPDATE " + Global.DB_PREFIX + "borrow_repayment SET status = :status,repayment_yes_time = :repaymentYesTime,");
        buffer.append("repayment_yes_account = :repaymentYesAccount WHERE status = 2 AND id = :id");
        Query query = em.createNativeQuery(buffer.toString());
        query.setParameter("status", repay.getStatus());
        query.setParameter("repaymentYesTime", repay.getRepaymentYesTime());
        query.setParameter("repaymentYesAccount", repay.getRepaymentYesAccount());
        query.setParameter("id", repay.getId());
        query.executeUpdate();
    }

    @Override
    public double getWaitInterest(long borrowId, int period) {
        String jpql = "select sum(interest) from BorrowRepayment br where br.status = 0  and br.borrow.id = ?1  and br.period >= ?2";
        Query q = em.createQuery(jpql).setParameter(1, borrowId);
        q.setParameter(2, period);
        Object ret = q.getSingleResult();
        if (ret == null) return 0;
        return (Double) ret;
    }

    @SuppressWarnings("unchecked")
    @Override
    public PageDataList<BorrowRepayment> getList(BorrowRepaymentModel model) {
        StringBuffer jpql = new StringBuffer("SELECT p1.* FROM " + Global.DB_PREFIX + "borrow_repayment p1, " + Global.DB_PREFIX + "borrow p2, " + Global.DB_PREFIX + "user p3");
        jpql.append(" WHERE p1.borrow_id = p2.id AND p2.user_id = p3.user_id and NOT EXISTS(SELECT * FROM " + Global.DB_PREFIX + "tpp_trade_log p4 WHERE p4.service_type='loan' and p4.trade_status != 'TRADE_FINISHED' and p4.borrow_id = p1.borrow_id) ");
        Map<String, Object> pars = Maps.newHashMap();
        if (model != null) {
            if (model.getVouchFirm() != null) {
                jpql.append(" AND p2.vouch_id = :vouchId");
                pars.put("vouchId", model.getVouchFirm().getUserId());
            } else {
                if (model.getBorrowId() > 0) {
                    jpql.append(" AND p1.borrow_id = :borrowId");
                    pars.put("borrowId", model.getBorrowId());
                }
                if (model.getUser() != null && model.getUser().getUserId() > 0) {
                    jpql.append(" AND p1.user_id = :userId");
                    pars.put("userId", model.getUser().getUserId());
                }
                if (StringUtil.isNotBlank(model.getUserName())) {
                    jpql.append(" AND p3.user_name LIKE :userName");
                    pars.put("userName", model.getUserName());
                }
                if (StringUtil.isNotBlank(model.getBorrowName())) {
                    jpql.append(" AND p2.name LIKE :borrowName");
                    pars.put("borrowName", "%" + model.getBorrowName() + "%");
                }

                // model.getTime() == 0 具体日期
                if (StringUtil.isNotBlank(model.getStartTime())) {
                    jpql.append(" AND p1.repayment_time >= :startTimeA");
                    pars.put("startTimeA", DateUtil.valueOf(model.getStartTime() + " 00:00:00"));
                }
                if (StringUtil.isNotBlank(model.getEndTime())) {
                    jpql.append(" AND p1.repayment_time <= :endTimeA");
                    pars.put("endTimeA", DateUtil.valueOf(model.getEndTime() + " 23:59:59"));
                }

                // -1,0 全部
                if (model.getTime() > 0) {
                    Date currentStartTime = DateUtil.getDayStartTime(System.currentTimeMillis() / 1000);
                    Date currentEndTime = DateUtil.getDayEndTime(System.currentTimeMillis() / 1000);
                    switch (model.getStatus()) {
                        case BorrowRepaymentStatus.STATUS_WAIT_REPAY:// 待还
                            jpql.append(" AND p1.status in (0,2) AND p1.repayment_time >= :startTimeA AND p1.repayment_time <= :endTimeA");
                            break;
                        case BorrowRepaymentStatus.STATUS_YES_REPAY:// 已还
                            jpql.append(" AND p1.status = 1 AND p1.repayment_time >= :startTimeA AND p1.repayment_time <= :endTimeA");
                            break;
                        case BorrowRepaymentStatus.UN_KNOWN:// 全部
                            jpql.append(" AND ((p1.status in (0,9) AND p1.repayment_time >= :startTimeA AND p1.repayment_time <= :endTimeA)"); // 预定还款时间
                            jpql.append(" OR (p1.status != 0 AND p1.repayment_yes_time >= :startTimeA AND p1.repayment_yes_time <= :endTimeA ))"); // 实际还款时间
                            break;
                        default: // 页面状态
                            jpql.append(" AND p1.status = :status AND p1.repayment_time >= :startTimeA AND p1.repayment_time <= :endTimeA");
                            pars.put("status", model.getStatus());
                            break;
                    }
                    // 时间范围
                    if (model.getTime() == 7) { // 最近7天 前后7天
                        pars.put("startTimeA", DateUtil.rollDay(currentStartTime, -7)); // 7天前开始时间00:00:00
                        pars.put("endTimeA", DateUtil.rollDay(currentEndTime, 7));     // 7天后的结束时间23:59:59
                    } else { // 最近1,2,3月
                        pars.put("startTimeA", DateUtil.rollMon(currentStartTime, -model.getTime())); // N月前开始时间00:00:00
                        pars.put("endTimeA", DateUtil.rollMon(currentEndTime, model.getTime()));     // N月后的结束时间23:59:59
                    }
                } else { // 时间不限 全部
                    if (model.getStatus() == BorrowRepaymentStatus.STATUS_WAIT_REPAY) {//如果status=0，则同样需要将网站垫付，逾期未还的查询出来
                        jpql.append(" AND p1.status in (0,2,9)");
                    } else if (model.getStatus() != BorrowRepaymentStatus.UN_KNOWN) { // 状态 不是全部 根据页面状态
                        jpql.append(" AND p1.status = :status");
                        pars.put("status", model.getStatus());
                    }
                }
            }
            if (model.isLate()) {
                if (model.getType() != 0) {
                    jpql.append(" AND p2.type = :type");
                    pars.put("type", model.getType());
                }
                jpql.append(" AND p1.repayment_time <= :repaymentTime");
                pars.put("repaymentTime", DateUtil.getNow());
                jpql.append(" AND p2.type <> 110 AND p2.type <> 101");
                jpql.append(" AND (p1.repayment_yes_time > p1.repayment_time OR p1.repayment_yes_account = 0 )");
            }
        }

        if (model.getStatus() == BorrowRepaymentStatus.STATUS_WAIT_REPAY) {
            jpql.append(" ORDER BY p1.repayment_time ASC"); // 预定还款时间
        } else if (model.getStatus() == BorrowRepaymentStatus.STATUS_YES_REPAY) {
            jpql.append(" ORDER BY p1.repayment_yes_time DESC"); // 已还款时间
        } else {
            jpql.append(" ORDER BY FIELD(p1.status,9,5,0,1,2,3,4), p1.repayment_yes_time DESC, repayment_time ASC");
        }
        Query query = em.createNativeQuery(jpql.toString(), BorrowRepayment.class);
        Iterator<Map.Entry<String, Object>> iterator = pars.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, Object> entry = iterator.next();
            query.setParameter(entry.getKey(), entry.getValue());
        }

        PageDataList<BorrowRepayment> pageDataList = new PageDataList<BorrowRepayment>();
        Page page = new Page(query.getResultList().size(), model.getPage(), model.getSize());
        if (model.getPage() == 0) {
            model.setPage(1);
        }
        query.setFirstResult((model.getPage() - 1) * model.getSize());
        query.setMaxResults(model.getSize());
        List<BorrowRepayment> list = query.getResultList();
        pageDataList.setList(list);
        pageDataList.setPage(page);
        return pageDataList;
    }

    @Override
    public int getCurrPeriod(long borrowId) {
        StringBuffer jpql = new StringBuffer("SELECT MIN(p1.period) FROM " + Global.DB_PREFIX + "borrow_repayment p1 WHERE p1.borrow_id = :borrowId AND (p1.status = 0 OR p1.status = 5)");
        Query q = em.createNativeQuery(jpql.toString()).setParameter("borrowId", borrowId);
        int currPeriod = 0;
        Object obj = q.getSingleResult();
        if (obj != null) {
            currPeriod = Integer.parseInt(obj.toString());
        }
        return currPeriod;
    }

    @Override
    public int getOverdueCount(long userId) {
        String sql = "SELECT COUNT(t2.id) FROM " + Global.DB_PREFIX + "borrow t1, " + Global.DB_PREFIX + "borrow_repayment t2 WHERE t1.vouch_id=:vouch_id AND t1.status IN ('6','7') AND t1.id = t2.borrow_id AND t2.status = '0' AND t2.repayment_time < NOW()";
        Query query = em.createNativeQuery(sql);
        query.setParameter("vouch_id", userId);
        Object count = query.getSingleResult();
        if (count != null) {
            return Integer.parseInt(count.toString());
        }
        return 0;
    }

    @Override
    public List<BorrowRepayment> getGuaranteeingList(String userId) {
        return null;
    }

    @Override
    public List<BorrowRepayment> getUrgeGuaranteeList(String userId) {
        return null;
    }

    @SuppressWarnings("unchecked")
    @Override
    public PageDataList<BorrowRepayment> getOverdueGuaranteeList(BorrowModel model) {
        StringBuffer sql = new StringBuffer("SELECT t2.* FROM " + Global.DB_PREFIX + "borrow t1, " + Global.DB_PREFIX + "borrow_repayment t2, " + Global.DB_PREFIX + "user t3 WHERE t1.vouch_id=:vouch_id AND t1.status IN ('6','7') AND t1.user_id = t3.user_id AND t1.id = t2.borrow_id AND t2.status = '0' AND t2.repayment_time < NOW()");

        String userName = "";
        if (StringUtil.isNotBlank(model.getUserName())) {
            userName = model.getUserName();
            sql.append(" AND t3.user_name LIKE :userName");
        }
        Date d = DateUtil.getDate(System.currentTimeMillis() / 1000 + "");
        Date startTime = null;
        Date endTime = null;
        if (model.getTime() == 7) {
            sql.append(" AND t2.add_time >= :startTime");
            sql.append(" AND t2.add_time <= :endTime");
            startTime = DateUtil.rollDay(d, -7);
            endTime = d;
        } else if (model.getTime() > 0 && model.getTime() < 4) {
            sql.append(" AND t2.add_time >= :startTime");
            sql.append(" AND t2.add_time <= :endTime");
            startTime = DateUtil.rollMon(d, -model.getTime());
            endTime = d;
        }
        if (StringUtil.isNotBlank(model.getStartTime())) {
            startTime = DateUtil.valueOf(model.getStartTime() + " 00:00:00");
            sql.append(" AND t2.add_time > :startTime");
        }
        if (StringUtil.isNotBlank(model.getEndTime())) {
            endTime = DateUtil.valueOf(model.getEndTime() + " 23:59:59");
            sql.append(" AND t2.add_time < :endTime");
        }

        Query query = em.createNativeQuery(sql.toString(), BorrowRepayment.class);
        query.setParameter("vouch_id", model.getVouchFirmId());

        if (startTime != null) {
            query.setParameter("startTime", startTime);
        }
        if (endTime != null) {
            query.setParameter("endTime", endTime);
        }

        if (userName.length() > 0) {
            query.setParameter("userName", "%" + userName.trim() + "%");
        }

        PageDataList<BorrowRepayment> pageDataList = new PageDataList<BorrowRepayment>();
        Page page = new Page(query.getResultList().size(), model.getPage(), model.getRows());
        if (model.getPage() == 0) {
            model.setPage(1);
        }

        query.setFirstResult((model.getPage() - 1) * model.getRows());
        query.setMaxResults(model.getRows());
        List<BorrowRepayment> list = query.getResultList();
        pageDataList.setList(list);
        pageDataList.setPage(page);
        return pageDataList;
    }

    @SuppressWarnings("unchecked")
    @Override
    public PageDataList<BorrowRepayment> getCompensatedList(BorrowModel model) {

        StringBuffer sql = new StringBuffer("SELECT t1.* FROM " + Global.DB_PREFIX + "borrow_repayment t1, " + Global.DB_PREFIX + "user t2 WHERE t1.user_id = t2.user_id AND t1.status = '1' AND t1.type = '2' AND t1.repayment_user_id = :vouch_id ");

        String userName = "";
        if (StringUtil.isNotBlank(model.getUserName())) {
            userName = model.getUserName();
            sql.append(" AND t2.user_name LIKE :userName");
        }
        Date d = DateUtil.getDate(System.currentTimeMillis() / 1000 + "");
        Date startTime = null;
        Date endTime = null;
        if (model.getTime() == 7) {
            sql.append(" AND t1.add_time >= :startTime");
            sql.append(" AND t1.add_time <= :endTime");
            startTime = DateUtil.rollDay(d, -7);
            endTime = d;
        } else if (model.getTime() > 0 && model.getTime() < 4) {
            sql.append(" AND t1.add_time >= :startTime");
            sql.append(" AND t1.add_time <= :endTime");
            startTime = DateUtil.rollMon(d, -model.getTime());
            endTime = d;
        }
        if (StringUtil.isNotBlank(model.getStartTime())) {
            startTime = DateUtil.valueOf(model.getStartTime() + " 00:00:00");
            sql.append(" AND t1.add_time > :startTime");
        }
        if (StringUtil.isNotBlank(model.getEndTime())) {
            endTime = DateUtil.valueOf(model.getEndTime() + " 23:59:59");
            sql.append(" AND t1.add_time < :endTime");
        }

        Query query = em.createNativeQuery(sql.toString(), BorrowRepayment.class);
        query.setParameter("vouch_id", model.getVouchFirmId());

        if (startTime != null) {
            query.setParameter("startTime", startTime);
        }
        if (endTime != null) {
            query.setParameter("endTime", endTime);
        }

        if (userName.length() > 0) {
            query.setParameter("userName", "%" + userName.trim() + "%");
        }

        PageDataList<BorrowRepayment> pageDataList = new PageDataList<BorrowRepayment>();
        Page page = new Page(query.getResultList().size(), model.getPage(), model.getRows());
        if (model.getPage() == 0) {
            model.setPage(1);
        }

        query.setFirstResult((model.getPage() - 1) * model.getRows());
        query.setMaxResults(model.getRows());
        List<BorrowRepayment> list = query.getResultList();
        pageDataList.setList(list);
        pageDataList.setPage(page);
        return pageDataList;
    }

    @Override
    public double getSumRepayAccount(long borrowId, int status) {
        String sql = "select sum(repayment_account) from " + Global.DB_PREFIX + "borrow_repayment  WHERE borrow_id = ? and status = ? and web_status =1";
        Query query = em.createNativeQuery(sql);
        query.setParameter(1, borrowId);
        query.setParameter(2, status);
        Object obj = query.getSingleResult();
        if (obj != null) {
            return Double.parseDouble(obj.toString());
        }
        return 0;
    }

    @Override
    public List<BorrowRepayment> findWaitRepay() {
        QueryParam param = QueryParam.getInstance();
        SearchFilter orFilter1 = new SearchFilter("status", Operators.EQ, BorrowRepaymentStatus.STATUS_WAIT_REPAY);
        SearchFilter orFilter2 = new SearchFilter("status", Operators.EQ, BorrowRepaymentStatus.STATUS_INSTEAD_REPAY);//网站垫付
        param.addOrFilter(orFilter1, orFilter2);
        return findByCriteria(param);
    }

    @Override
    public int getMaxPeriod(long borrowId) {
        String sql = "select max(period) from " + Global.DB_PREFIX + "borrow_repayment where status=1 and borrow_id=?";
        int period = 0;
        try {
            Query query = em.createNativeQuery(sql);
            period = (Integer) query.getSingleResult();
        } catch (Exception e) {

        }
        return period + 1;
    }

    @Override
    public int getMinPeriod(long borrowId) {
        String sql = "select ifnull(min(period),0) from " + Global.DB_PREFIX + "borrow_repayment where borrow_id = ? and status=0";
        try {
            Query query = em.createNativeQuery(sql).setParameter(1, borrowId);
            Object obj = query.getSingleResult();
            if (obj != null) {
                return Integer.parseInt(obj.toString());
            }
        } catch (Exception e) {

        }
        return 0;
    }

    @Override
    public double getRemainderAccount() {
        String jpql = "select sum(br.capital) from " + Global.DB_PREFIX + "borrow_repayment br where br.status in (0,2) ";
        Query q = em.createNativeQuery(jpql);
        Object ret = q.getSingleResult();
        if(ret == null) return 0;
        return Double.parseDouble(ret.toString());
    }


    @Override
    public Object[] getRepaymentTimeAndMaxPeriod(long borrowId) {
        String jpql = "select repayment_time,max(period) from " + Global.DB_PREFIX + "borrow_repayment where status=0 and borrow_id=?";
        Query query = em.createNativeQuery(jpql).setParameter(1, borrowId);
        Object obj = query.getSingleResult();
        return (Object[]) obj;
    }

    @Override
    public double[] getOverdueByDate(String date) {
        String sql = "select count(distinct user_id),sum(repayment_account) from " + Global.DB_PREFIX + "borrow_repayment where status != 1 and repayment_time < now() and date_format(repayment_time,'%Y-%m') = ?";
        Query query = em.createNativeQuery(sql).setParameter(1, date);
        Object[] obj = (Object[]) query.getSingleResult();
        double[] vals = new double[2];
        vals[0] = Double.parseDouble(obj[0] + "");
        if (obj[1] != null) {
            vals[1] = Double.parseDouble(obj[1] + "");
        }
        return vals;
    }

    @Override
    public double getOverdueByTypeAndDate(int type, String date) {
        StringBuffer sb = new StringBuffer("select ");
        if (type == 1) {
            sb.append("count(distinct user_id)");
        } else {
            sb.append("sum(repayment_account)");
        }
        sb.append(" from " + Global.DB_PREFIX + "borrow_repayment where status != 1 and repayment_time < now() and date_format(repayment_time,'%Y-%m') = ?");
        Query query = em.createNativeQuery(sb.toString()).setParameter(1, date);
        Object obj = query.getSingleResult();
        double result = 0;
        if (obj != null) {
            result = Double.parseDouble(obj + "");
        }
        return result;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> getOverdueCardIdsByDate(String date) {
        String sql = "select distinct(card_id) from " + Global.DB_PREFIX + "user u, " + Global.DB_PREFIX + "borrow_repayment r where u.user_id=r.user_id and status != 1 and repayment_time < now() and date_format(repayment_time,'%Y-%m')=?";
        Query query = em.createNativeQuery(sql).setParameter(1, date);
        return query.getResultList();
    }
}